package com.app;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

class ClickhouseDemo {

	public static void main(String[] args) {
		String sqlDB = "show databases";// 查询数据库
		String sqlTab = "show tables";// 查看表
		String sqlCount = "select count(*) count from tutorial.visits_v1";// 
		String sql="SELECT StartURL AS URL,AVG(Duration) AS AvgDuration FROM tutorial.visits_v1 WHERE StartDate BETWEEN '2014-03-23' AND '2014-03-30' GROUP BY URL ORDER BY AvgDuration DESC LIMIT 10";
			    
			
			
			
			
		exeSql(sqlDB);
		exeSql(sqlTab);
		exeSql(sqlCount);
		exeSql(sql);
	}

	public static void exeSql(String sql) {
		String address = "jdbc:clickhouse://172.16.10.114:8123/default";
		Connection connection = null;
		Statement statement = null;
		ResultSet results = null;
		try {
			Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
			connection = DriverManager.getConnection(address);
			statement = connection.createStatement();
			long begin = System.currentTimeMillis();
			results = statement.executeQuery(sql);
			long end = System.currentTimeMillis();
			System.out.println("执行（" + sql + "）耗时：" + (end - begin) + "ms");
			ResultSetMetaData rsmd = results.getMetaData();
			List<Map<String,Object>> list = new ArrayList<>();
			while (results.next()) {
				Map<String,Object> map = new HashMap<>();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					map.put(rsmd.getColumnName(i), results.getString(rsmd.getColumnName(i)));
				}
				list.add(map);
			}
			for (Map<String,Object> map : list) {
				System.err.println(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {// 关闭连接
			try {
				if (results != null) {
					results.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}
